package coutom.sink.utils.excel;

import coutom.sink.bean.Person;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.io.IOException;
import java.lang.Boolean;
import java.util.ArrayList;
import java.util.List;

/**
 * @author : ZhaoY
 * @description :用于操作excel的工具
 * @date : 2021-03-13 11:25
 **/
@Slf4j
public class ExcelUtils {
    //写入的行数
    static int lineNumber = 0;

    public static Boolean WriterExcel(File file, List<Person> people) {
        WritableWorkbook workbook = null;
        try {
            workbook = Workbook.createWorkbook(file);
            WritableSheet ws = workbook.createSheet("mysheet", 0);
            //设置各列的长度
            ws.setColumnView(0, 30);
            ws.setColumnView(1, 30);
            ws.setColumnView(2, 30);
            lineNumber = 0;
            for (Person person1 : people) {
                ws.addCell(new Label(0, lineNumber, person1.getId().toString()));
                ws.addCell(new Label(1, lineNumber, person1.getName()));
                ws.addCell(new Label(2, lineNumber, person1.getNumber()));
                lineNumber++;
            }
            //写入到文件
            workbook.write();
            //释放资源
            workbook.close();
        } catch (IOException e) {
            log.info("文件读取错误");
            return false;
        } catch (RowsExceededException e) {
            log.info("Excel资源写入错误");
            return false;
        } catch (WriteException e) {
            log.info("释放Excel连接资源错误");
            return false;
        }
        return true;
    }

    public static Boolean updateExcel(File file, List<Person> people) {
        //创建只读的Excel工作薄的对象
        Workbook rw = null;
        //创建可写入的Excel工作薄对象
        WritableWorkbook wwb = null;
        try {

            rw = Workbook.getWorkbook(file);
            wwb = Workbook.createWorkbook(file, rw);
            //读取第一张工作表
            WritableSheet ws = wwb.getSheet(0);
            //获得末尾的第一个空白单元格对象

            for (Person person1 : people) {
                ws.addCell(new Label(0, lineNumber, person1.getId().toString()));
                ws.addCell(new Label(1, lineNumber, person1.getName()));
                ws.addCell(new Label(2, lineNumber, person1.getNumber()));
                lineNumber++;
            }
            //写入Excel对象
            wwb.write();
            //关闭可写入的Excel对象
            wwb.close();
            //关闭只读的Excel对象
            rw.close();
        } catch (IOException e) {
            log.info("更新文件读取错误");
            return false;
        } catch (BiffException e) {
            log.info("更新Excel资源写入错误");
            return false;
        } catch (WriteException e) {
            log.info("更新释放Excel连接资源错误");
            return false;
        }
        return true;
    }

    public static Boolean updateOneExcel(File file, Person person) {
        List<Person> people = new ArrayList<>();
        people.add(person);
        return updateExcel(file, people);
    }
}
